# -*- coding: utf-8 -*-
from db_conn import *
import re
import time
import traceback
from warnings import filterwarnings
from datetime import datetime,timedelta

filterwarnings('error', category=MySQLdb.Warning)

date_now = datetime.now()
in_file_name_1 = "" + date_now.strftime("%Y-%m-%d") + "_file1.txt"
in_file_name_2 = "" + date_now.strftime("%Y-%m-%d") + "_file2.txt"
export_file_id = "" + date_now.strftime("%Y-%m-%d") + "-export-db-id.txt"
export_file = "" + date_now.strftime("%Y-%m-%d") + "-export-db.txt"
double_check_file = "" + date_now.strftime("%Y-%m-%d") + "-double_check.txt"
line_txt=""

def getLineByNumber(line_nbr,file_name):
    linesByNbr=execServerLit("sed -n '%sp' %s" % (line_nbr,file_name))
    #print "linesByNbr: %s" % linesByNbr
    return linesByNbr

def getDbIdFunc(line):   
    try:
        func = getLineByNumber(getMatchedLine(line,export_file),export_file_id)
        #print "func: %s" % func
        return func.split("|")[-1]
    except:
        return "0"
    
def getLineCount(file_name):
    linesCount=execServerLit('wc -l %s' % (file_name))
    return linesCount

def delMatchedLine(line_nbr,file_name):
    execServerLit("sed --in-place '%sd' %s" % (line_nbr,file_name)) 

def getMatchedLine(s1,file_name):
    resposta=execServerLit('grep -n -F "%s" %s' % (s1.replace("\n",""),file_name))    
    try:
        return str(resposta).split(":")[0]
    except:
        return 0    
    
def execServerLit(command):
    import os
    #print command
    execute_command = os.popen(command,"r")
    return execute_command.read()

def import_txt(arquivo_import):
    try:
        cursor,conn = db_connect()
        count_ln=0
        
        #field_names = {}
        #for i in range(len(cursor.description)):
        #    field_names[cursor.description[i][0]]=i-1
        #print field_names     
        field_names = ['TransmissaoInicial',
                         'UnidadeFrequenciaTX',
                         'RecepcaoInicial',
                         'UnidadeFrequenciaRX',
                         'Situacao',
                         'CaraterSecundario',
                         'IndConfidencial',
                         'TipoClasseEstacao',
                         'Horarios',
                         'DesignacaoEmissao',
                         'PotenciaOperacao',
                         'UnidadePotencia',
                         'Equipamento',
                         'TipoAntena',
                         'EquipamentoAntena',
                         'Ganho',
                         'FrenteCosta',
                         'AnguloMeiaPotencia',
                         'AnguloElevacao',
                         'Azimute',
                         'VariacaoAzimutal',
                         'TipoPolarizacao',
                         'Altura',
                         'Raio',
                         'Latitude',
                         'Longitude',
                         'Altitude',
                         'Estacao',
                         'Servico',
                         'Indicativo',
                         'MedPotenciaMaxima_TR',
                         'MedLarguraBanda',
                         'DiametroSGCH',
                         'Diagrama',
                         'SiglaEstacao_TRS',
                         'LongitudeOrbitalSubida',
                         'NomeFeixeSubida',
                         'SiglaEstacaoDescida',
                         'LongitudeOrbitalDescida',
                         'NomeFeixeDescida',
                         'TipoEstacao',
                         'NomeEntidade',
                         'NumEntidade',
                         'NomeMunicipio',
                         'CodMunicipio',
                         'Endereco',
                         'UF',
                         'TipoClasseCorrespondencia',
                         'Rede',
                         'Distancia',]
        dict_fields={}        
        for f in range(len(field_names)):
            dict_fields[field_names[f]]=f
        with open(arquivo_import) as infile:
            for line in infile:
                
                start_parcial_select = time.time()
                
                select_values = ""
                line_fix = line.replace("\r","").replace("\n","").split("|")
                for col in range(len(line_fix)):
                    #insert_values += """'%s',""" % (re.escape(col))
                    #insert_values += """'%s',""" % (str(MySQLdb.escape_string(col)))
                    select_values += """%s = '%s' and """ % (field_names[col],str(MySQLdb.escape_string(line_fix[col])))
                
                select_anatel="""
                SELECT id_antena from anatel_import
                where 
                %s
                1 = 1
                """ % (select_values)
                #print select_anatel
                try:
                    cursor.execute(select_anatel)
                    resp = cursor.fetchall()
                    if len(resp)>0:
                        for r in resp:
                            print r
                except MySQLdb.Warning, e:
                    # handle warnings, if the cursor you're using raises them
                    arr_campo = re.findall("'([^']*)'", str(e));
                    print arr_campo
                    #print insert_anatel
                    #print col[field_names[arr_campo[0].replace("'","")]]
                    print line.split("|")[int(dict_fields[arr_campo[0].replace("'","")])]    
                    continue                
                except:
                    print "Erro ao atualizar o banco"
                    exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
                    traceback.print_exception(exceptionType, exceptionValue, exceptionTraceback,
                                      limit=2, file=sys.stdout)
                    
                print "(Tempo Gasto Select: %s)" % ((time.time() - start_parcial_select))
                
                start_parcial_linux = time.time()
                
                print getDbIdFunc(line.replace("\r","").replace("\n",""))
                
                print "(Tempo Gasto Linux: %s)" % ((time.time() - start_parcial_linux))
                
                count_ln+=1
                if count_ln%1000==0:
                    print "Counter: %s" % (count_ln)
        db_disconnect(cursor,conn)
    except:
        print "Erro ao atualizar o banco"
        exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
        traceback.print_exception(exceptionType, exceptionValue, exceptionTraceback,
                          limit=2, file=sys.stdout)


def getAntenaId(line_txt):
    try:
        cursor,conn = db_connect()
        count_ln=0
        
        #field_names = {}
        #for i in range(len(cursor.description)):
        #    field_names[cursor.description[i][0]]=i-1
        #print field_names     
        field_names = ['TransmissaoInicial',
                         'UnidadeFrequenciaTX',
                         'RecepcaoInicial',
                         'UnidadeFrequenciaRX',
                         'Situacao',
                         'CaraterSecundario',
                         'IndConfidencial',
                         'TipoClasseEstacao',
                         'Horarios',
                         'DesignacaoEmissao',
                         'PotenciaOperacao',
                         'UnidadePotencia',
                         'Equipamento',
                         'TipoAntena',
                         'EquipamentoAntena',
                         'Ganho',
                         'FrenteCosta',
                         'AnguloMeiaPotencia',
                         'AnguloElevacao',
                         'Azimute',
                         'VariacaoAzimutal',
                         'TipoPolarizacao',
                         'Altura',
                         'Raio',
                         'Latitude',
                         'Longitude',
                         'Altitude',
                         'Estacao',
                         'Servico',
                         'Indicativo',
                         'MedPotenciaMaxima_TR',
                         'MedLarguraBanda',
                         'DiametroSGCH',
                         'Diagrama',
                         'SiglaEstacao_TRS',
                         'LongitudeOrbitalSubida',
                         'NomeFeixeSubida',
                         'SiglaEstacaoDescida',
                         'LongitudeOrbitalDescida',
                         'NomeFeixeDescida',
                         'TipoEstacao',
                         'NomeEntidade',
                         'NumEntidade',
                         'NomeMunicipio',
                         'CodMunicipio',
                         'Endereco',
                         'UF',
                         'TipoClasseCorrespondencia',
                         'Rede',
                         'Distancia',]
        dict_fields={}        
        for f in range(len(field_names)):
            dict_fields[field_names[f]]=f

        line = line_txt 
                
        start_parcial_select = time.time()
        
        select_values = ""
        line_fix = line.replace("\r","").replace("\n","").split("|")
        for col in range(len(line_fix)):
            #insert_values += """'%s',""" % (re.escape(col))
            #insert_values += """'%s',""" % (str(MySQLdb.escape_string(col)))
            #select_values += """%s = '%s' and """ % (field_names[col],str(MySQLdb.escape_string(line_fix[col])))
            if len(str(line_fix[col]).strip()) > 0:
                select_values += """%s = '%s' and """ % (field_names[col],str(MySQLdb.escape_string(line_fix[col])))
        
        select_anatel="""
        SELECT id_antena from anatel_real
        where 
        %s
        1 = 1
        """ % (select_values)
        #print select_anatel
        try:
            print select_anatel
            cursor.execute(select_anatel)
            resp = cursor.fetchall()
            if len(resp)>0:
                for r in resp:
                    print r
        except MySQLdb.Warning, e:
            # handle warnings, if the cursor you're using raises them
            arr_campo = re.findall("'([^']*)'", str(e));
            print arr_campo
            #print insert_anatel
            #print col[field_names[arr_campo[0].replace("'","")]]
            print line.split("|")[int(dict_fields[arr_campo[0].replace("'","")])]    
        except:
            print "Erro ao atualizar o banco"
            exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
            traceback.print_exception(exceptionType, exceptionValue, exceptionTraceback,
                              limit=2, file=sys.stdout)
            
        print "(Tempo Gasto Select: %s)" % ((time.time() - start_parcial_select))
        
        start_parcial_linux = time.time()
        
        print getDbIdFunc(line.replace("\r","").replace("\n",""))
        
        print "(Tempo Gasto Linux: %s)" % ((time.time() - start_parcial_linux))
        
        count_ln+=1
        if count_ln%1000==0:
            print "Counter: %s" % (count_ln)
        db_disconnect(cursor,conn)
    except:
        print "Erro ao atualizar o banco"
        exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
        traceback.print_exception(exceptionType, exceptionValue, exceptionTraceback,
                          limit=2, file=sys.stdout)

if __name__ == '__main__':
    if not len(sys.argv) < 2:
        
        for v in sys.argv:
            if len(v.split("="))>1:
                param = v.split("=")[0].strip()
                valor = v.split("=")[1].strip()
                try:
                    if param.lower().strip().find("arquivo_import")>-1:
                        arquivo_import = valor
                        print "arquivo_import: %s" % (arquivo_import)
                    elif param.lower().strip().find("line_txt")>-1:
                        line_txt = valor
                        print "line_txt: %s" % (line_txt)
                    elif param.lower().strip().find("date_custom")>-1:
                        date_custom = valor
                        print "date_custom: %s" % (date_custom)
                        in_file_name_1 = "" + date_custom + "_file1.txt"
                        in_file_name_2 = "" + date_custom + "_file2.txt"
                        export_file_id = "" + date_custom + "-export-db-id.txt"
                        export_file = "" + date_custom + "-export-db.txt"   
                        double_check_file = "" + date_custom + "-double_check.txt"                                
                    else:
                        eval('%s=%s' % (param,valor))   
                except:
                    exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
                    print "%s:%s" % (exceptionType,exceptionValue)
            else:
                line_txt = v.strip()
    
    
    start_total = time.time()
    
    print "(Comecando!)"
            
    #print "Iniciando import arquivo: %s" % (arquivo_import)
    #import_txt(arquivo_import)
    getAntenaId(line_txt)
    #print "Fim import!"
    
    print "(Total Tempo Gasto: %s)" % ((time.time() - start_total))
    